package util;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.HashMap;
import java.util.Map;

/**
 * 出行记录数据访问对象类，处理出行记录的增删改查
 */
public class TravelRecordDAO {

    // 添加新的出行记录
    public boolean addTravelRecord(TravelRecord record) {
        String sql = "INSERT INTO travel_records (user_id, travel_date, travel_type, " +
                "start_location, end_location, distance, earned_points, carbon_reduction) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, record.getUserId());
            pstmt.setTimestamp(2, new Timestamp(record.getTravelDate().getTime()));
            pstmt.setString(3, record.getTravelType());
            pstmt.setString(4, record.getStartLocation());
            pstmt.setString(5, record.getEndLocation());
            pstmt.setDouble(6, record.getDistance());
            pstmt.setInt(7, record.getEarnedPoints());
            pstmt.setDouble(8, record.getCarbonReduction());

            int affectedRows = pstmt.executeUpdate();

            if (affectedRows > 0) {
                // 更新用户的积分和碳减排量
                UserDAO userDAO = new UserDAO();
                userDAO.updatePointsAndCarbonReduction(record.getUserId(), record.getEarnedPoints(),
                        record.getCarbonReduction());
                return true;
            }

            return false;
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.closeConnection(conn, pstmt, null);
        }
    }

    // 获取用户的所有出行记录
    public List<TravelRecord> getUserTravelRecords(int userId) {
        String sql = "SELECT * FROM travel_records WHERE user_id = ? ORDER BY travel_date DESC";

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<TravelRecord> records = new ArrayList<>();

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);

            rs = pstmt.executeQuery();

            while (rs.next()) {
                TravelRecord record = new TravelRecord();
                record.setId(rs.getInt("id"));
                record.setUserId(rs.getInt("user_id"));
                record.setTravelDate(new Date(rs.getTimestamp("travel_date").getTime()));
                record.setTravelType(rs.getString("travel_type"));
                record.setStartLocation(rs.getString("start_location"));
                record.setEndLocation(rs.getString("end_location"));
                record.setDistance(rs.getDouble("distance"));

                records.add(record);
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeConnection(conn, pstmt, rs);
        }

        return records;
    }

    // 获取用户的出行统计数据
    public Map<String, Integer> getUserTravelStats(int userId) {
        String sql = "SELECT travel_type, COUNT(*) as count FROM travel_records WHERE user_id = ? GROUP BY travel_type";

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Map<String, Integer> stats = new HashMap<>();

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);

            rs = pstmt.executeQuery();

            while (rs.next()) {
                String travelType = rs.getString("travel_type");
                int count = rs.getInt("count");
                stats.put(travelType, count);
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeConnection(conn, pstmt, rs);
        }

        return stats;
    }

    // 获取用户的平均出行距离
    public double getUserAverageDistance(int userId) {
        String sql = "SELECT AVG(distance) as avg_distance FROM travel_records WHERE user_id = ?";

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);

            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getDouble("avg_distance");
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeConnection(conn, pstmt, rs);
        }

        return 0.0;
    }

    // 获取总体碳减排统计
    public double getTotalCarbonReduction() {
        String sql = "SELECT SUM(carbon_reduction) as total FROM travel_records";

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            if (rs.next()) {
                return rs.getDouble("total");
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeConnection(conn, stmt, rs);
        }

        return 0.0;
    }
}
